package com.imis.base.util.table;

import com.imis.base.constant.CommonConstant;
import com.imis.base.constant.DataBaseConstant;
import com.imis.base.constant.enums.CommonResponseEnum;
import com.imis.base.util.ConvertUtils;
import com.imis.base.util.SpringContextUtils;
import com.imis.base.util.table.service.IDataBaseTableHandleService;
import com.imis.base.util.table.service.impl.DataBaseTableMySqlHandleServiceImpl;
import com.imis.base.util.table.service.impl.DataBaseTableOracleHandleServiceImpl;
import com.imis.base.util.table.service.impl.DataBaseTablePostgresHandleServiceImpl;
import com.imis.base.util.table.service.impl.DataBaseTableSqlServerHandleServiceImpl;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * DataBaseTableUtil<br>
 * 数据库表工具类
 * </p>
 *
 * @author XinLau
 * @since 2020-03-12
 */
@Slf4j
public class DataBaseTableUtil {

    /**
     * 数据库类型
     */
    public static String DB_TYPE = "";

    /**
     * 获取数据库类型
     *
     * @return String - 数据库类型
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 9:45
     */
    public static String getDataBaseType() throws SQLException {
        if (ConvertUtils.isNotEmpty(DataBaseTableUtil.DB_TYPE)) {
            return DataBaseTableUtil.DB_TYPE;
        }
        DataSource dataSource = SpringContextUtils.getBean(DataSource.class);
        return getDataBaseType(dataSource);
    }

    /**
     * 根据数据库类型构造不同 handler
     *
     * @return IDataBaseTableHandleService - 数据库表处理服务
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 9:42
     */
    public static IDataBaseTableHandleService getTableHandle() throws SQLException {
        IDataBaseTableHandleService dataBaseTableHandleService = null;
        String dbType = getDataBaseType();
        switch (dbType) {
            case DataBaseConstant.DB_TYPE_MYSQL:
                dataBaseTableHandleService = new DataBaseTableMySqlHandleServiceImpl();
                break;
            case DataBaseConstant.DB_TYPE_ORACLE:
                dataBaseTableHandleService = new DataBaseTableOracleHandleServiceImpl();
                break;
            case DataBaseConstant.DB_TYPE_SQL_SERVER:
                dataBaseTableHandleService = new DataBaseTableSqlServerHandleServiceImpl();
                break;
            case DataBaseConstant.DB_TYPE_POSTGRE_SQL:
                dataBaseTableHandleService = new DataBaseTablePostgresHandleServiceImpl();
                break;
            default:
                break;
        }
        return dataBaseTableHandleService;
    }

    /**
     * 获取数据库连接对象
     *
     * @return String - 数据库类型
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 9:45
     */
    public static Connection getConnection() throws SQLException {
        /*
         * TODO:This DataSource 多数据源产生问题
         */
        DataSource dataSource = SpringContextUtils.getBean(DataSource.class);
        return dataSource.getConnection();
    }

    /**
     * 判断是不是 Oracle 数据库 是的话存在大小写问题
     *
     * @return Boolean - 有无字母大小写问题
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 9:48
     */
    public static Boolean hasLetterCaseProblem() {
        try {
            return DataBaseConstant.DB_TYPE_ORACLE.equals(getDataBaseType());
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        return Boolean.FALSE;
    }

    /**
     * 获取数据库类型
     *
     * @param dataSource - DataSource
     * @return String - 数据库类型
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 9:50
     */
    public static String getDataBaseType(DataSource dataSource) {
        if (CommonConstant.EMPTY.equals(DB_TYPE)) {
            try (Connection connection = dataSource.getConnection()) {
                return getDataBaseType(connection);
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
        return DB_TYPE;
    }

    /**
     * 根据 connection 对象获取数据库类型<br>
     * 调用此方法需要自己在外层手动关闭 connection
     *
     * @param connection - Connection
     * @return String - 数据库类型
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 9:50
     */
    public static String getDataBaseType(Connection connection) throws SQLException {
        if (CommonConstant.EMPTY.equals(DB_TYPE)) {
            DatabaseMetaData md = connection.getMetaData();
            String dataBaseType = md.getDatabaseProductName().toLowerCase();
            if (dataBaseType.contains(DataBaseConstant.MYSQL)) {
                DB_TYPE = DataBaseConstant.DB_TYPE_MYSQL;
            } else if (dataBaseType.contains(DataBaseConstant.ORACLE)) {
                DB_TYPE = DataBaseConstant.DB_TYPE_ORACLE;
            } else if (dataBaseType.contains(DataBaseConstant.SQLSERVER) || dataBaseType.contains(DataBaseConstant.SQL_SERVER)) {
                DB_TYPE = DataBaseConstant.DB_TYPE_SQL_SERVER;
            } else if (dataBaseType.contains(DataBaseConstant.POSTGRE_SQL)) {
                DB_TYPE = DataBaseConstant.DB_TYPE_POSTGRE_SQL;
            } else {
                CommonResponseEnum.ERROR_500.assertFail("数据库类型:[" + dataBaseType + "]不识别!");
            }
        }
        return DB_TYPE;
    }

    /**
     * 根据数据库类型调整数据库表名
     *
     * @param tableName    - 表名
     * @param dataBaseType - 数据库类型
     * @return String -
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 10:28
     */
    public static String getTableName(String tableName, String dataBaseType) {
        switch (dataBaseType) {
            case DataBaseConstant.DB_TYPE_ORACLE:
                return tableName.toUpperCase();
            case DataBaseConstant.DB_TYPE_POSTGRE_SQL:
                return tableName.toLowerCase();
            default:
                return tableName;
        }
    }

    /**
     * 根据数据库表名判断数据库表是否存在
     *
     * @param tableName - 数据库表名
     * @return Boolean -
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 10:30
     */
    public static Boolean judgeTableIsExit(String tableName) {
        Connection conn = null;
        ResultSet rs = null;
        try {
            String[] types = {"TABLE"};
            conn = DataBaseTableUtil.getConnection();
            DatabaseMetaData dbMetaData = conn.getMetaData();
            String dataBaseType = dbMetaData.getDatabaseProductName().toUpperCase();
            String tableNamePattern = getTableName(tableName, dataBaseType);
            rs = dbMetaData.getTables(null, null, tableNamePattern, types);
            if (rs.next()) {
                log.info("数据库表：【" + tableName + "】已存在");
                return Boolean.TRUE;
            } else {
                return Boolean.FALSE;
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        } finally {
            // 关闭连接
            try {
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    /**
     * 获取列的 Map<br>
     * key 是 column_name value 是 List<Map<String, Object>>
     *
     * @param queryForList - Map<String, Object>
     * @return Map<String, Object> -
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 10:32
     */
    public static Map<String, Object> getColumnMap(List<Map<String, Object>> queryForList) {
        Map<String, Object> columnMap = new HashMap<>(queryForList.size());
        for (Map<String, Object> stringObjectMap : queryForList) {
            columnMap.put(stringObjectMap.get("column_name").toString(), stringObjectMap);
        }
        return columnMap;
    }

    /**
     * 获取数据库方言
     *
     * @return String - 数据库方言
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 10:44
     */
    public static String getDataBaseDialect() throws SQLException {
        // TODO:This：方言获取,此方法不能通用那就直接在yml中配置死
        String databaseType = getDataBaseType();
        return getDataBaseDialect(databaseType);
    }

    /**
     * 获取数据库方言
     *
     * @param dataBaseType - 数据库类型
     * @return String - 数据库方言
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/9/30 10:44
     */
    public static String getDataBaseDialect(String dataBaseType) {
        // String dialect = "org.hibernate.dialect.MySQLDialect";
        String dialect = "org.hibernate.dialect.MySQL5InnoDBDialect";
        switch (dataBaseType) {
            case DataBaseConstant.DB_TYPE_SQL_SERVER:
                dialect = "org.hibernate.dialect.SQLServerDialect";
                break;
            case DataBaseConstant.DB_TYPE_POSTGRE_SQL:
                dialect = "org.hibernate.dialect.PostgreSQLDialect";
                break;
            case DataBaseConstant.DB_TYPE_ORACLE:
                dialect = "org.hibernate.dialect.OracleDialect";
                break;
            default:
                break;
        }
        return dialect;
    }

}
